use lavender::db::query_statement::prelude::*;
use lavender::*;
use lavender::db::model::prelude::*;
use lavender::db::table::{ExceResult,Table,ExecutorBase,TableExecutor};


pub struct BaseTable{
    pub sql_maker:Option<SqlMakerRaw>,
}

impl Table for BaseTable{
    const TABLE_NAME:&'static str = "test";
    const DATABASE_NAME:&'static str = "test";
    lavender_default_table_property!();
}


#[tokio::test]
async fn get_test(){
    let res = lavender_model_query!(find_by_pk 25,lavender_query_filters!({"datenum" > 1},{"day" >= 0}));
    assert!(res.is_ok());    
    let maker = res.ok().unwrap();
    let res = BaseTable::with_sql_maker(maker).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query1 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);
    let res = BaseTable::with_sql_maker(lavender_model_query!(find_by_pk 20).unwrap()).build_executor().unwrap().one().await;
    assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20)).unwrap()).build_executor().unwrap().one().await;
    assert!(res.is_ok(),"query3 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20),lavender_query_fields!("id","day")).unwrap()).build_executor().unwrap().one().await;
    assert!(res.is_ok(),"query4 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC").unwrap()).build_executor().unwrap().one().await;
    assert!(res.is_ok(),"query5 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20)).unwrap()).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query6 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day")).unwrap()).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query7 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC").unwrap()).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query8 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC",10).unwrap()).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query9 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(find_all lavender_query_filter!("id" > 20),lavender_query_fields!("id","day"),"id DESC",5,10).unwrap()).build_executor().unwrap().all().await;
    assert!(res.is_ok(),"query10 failed. msg:{:?}",res.err().unwrap());  
    println!("{:?}",res);

    
    let res = BaseTable::with_sql_maker(lavender_model_query!(add lavender_query_key_values!({"datenum" 123},{"day" 456},{"time" 789})).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query11 failed. msg:{:?}",res.err().unwrap());  
    let insert_info = res.unwrap();
    println!("{:?}",insert_info);
    let insert_id = insert_info.last_insert_id();
    let affected = insert_info.rows_affected();
    assert!(affected > 0,"add data failed");

    let res = BaseTable::with_sql_maker(lavender_model_query!(update insert_id, lavender_query_key_values!({"datenum" 0},{"day" 111},{"time" 222})).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query12 failed. msg:{:?}",res.err().unwrap());  
    assert!(insert_info.rows_affected() > 0,"update data failed");

    let res = BaseTable::with_sql_maker(lavender_model_query!(update insert_id, lavender_query_key_values!({"datenum" 0},{"day" 111},{"time" 222}),lavender_query_filter!("datenum" > 0)).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query13 failed. msg:{:?}",res.err().unwrap());  
    let res = res.unwrap();
    assert!(res.rows_affected() == 0,"update data failed {:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(update_all lavender_query_filter!("id" >= insert_id),lavender_query_key_values!({"datenum" 3},{"day" 3},{"time" 3})).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query14 failed. msg:{:?}",res.err().unwrap());
    let res = res.unwrap();
    assert!(res.rows_affected() > 0,"update data failed {:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(upsert lavender_query_key_values!({"id" insert_id},{"datenum" 4},{"day" 4},{"time" 4}),lavender_query_key_values!({"time" 5})).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query15 failed. msg:{:?}",res.err().unwrap());
    println!("upsert res {:?}",res.unwrap());

    let res = BaseTable::with_sql_maker(lavender_model_query!(delete insert_id,lavender_query_filter!("time" 5)).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query16 failed. msg:{:?}",res.err().unwrap());
    let res = res.unwrap();
    println!("delete res {:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(delete insert_id).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query17 failed. msg:{:?}",res.err().unwrap());
    let res = res.unwrap();
    println!("delete res {:?}",res);

}


type DeviceBase = (i32,String,i16,i16,i64,i64);

fn get_test_mutil_insert_data(len:i32) -> Vec<DeviceBase>{
    let mut res = vec![];
    let tn = utils::timestamp();
    for i in 0..len  {
        res.push((i ,format!("sn_{}",i*i), (i % 10) as i16,(i / 10 % 10) as i16,tn,tn + (i as i64) + 1));
    }
    res
}

#[tokio::test]
async fn test_bulk(){
    let num = 5000;
    let mut column = lavender_query_fields!("datenum","day","time");
    let mutil_data = get_test_mutil_insert_data(num);
    column.push_values_convert(mutil_data, |item|{
        format!("{},{},{}",item.5,item.0,item.3).into()
    });

    let model = BaseModel::bulk_insert(column).unwrap();

    let res = BaseTable::with_sql_maker(model).build_executor().unwrap().set_persistent(false).exec().await;
    assert!(res.is_ok(),"query1 failed. msg:{:?}",res.err().unwrap());  
    let insert_info = res.unwrap();
    println!("{:?}",insert_info);
    let insert_id = insert_info.last_insert_id();
    let affected = insert_info.rows_affected();
    assert!(affected == num as u64,"add bulk data failed");

    let res = BaseTable::with_sql_maker(lavender_model_query!(delete_all lavender_query_filter!("id" > insert_id),1000).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());
    let res = res.unwrap();
    println!("delete res {:?}",res);

    let res = BaseTable::with_sql_maker(lavender_model_query!(delete_all lavender_query_filter!("id" >= insert_id)).unwrap()).build_executor().unwrap().exec().await;
    assert!(res.is_ok(),"query2 failed. msg:{:?}",res.err().unwrap());
    let res = res.unwrap();
    println!("delete res {:?}",res);
}